package com.hatricks.is.base.mysql.batch;

import cn.hutool.core.date.StopWatch;
import cn.hutool.core.util.ClassLoaderUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.RandomUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.concurrent.TimeUnit;

/**
 * @author: hatricks
 * @description: 使用原生JDBC插入数据库10万条, 实测大概2s
 */
public class BatchInsertDatas {

    private static final String URL = "jdbc:mysql://zbcommonstag.mysql.rds.aliyuncs.com/db_cost_data_platform?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&useSSL=false";
    private static final String USER = "gcdp_admin";
    private static final String PASSWORD = "T97^TmZGgy9fQOE";

    public static void main(String[] args) {
        StopWatch stopWatch = new StopWatch("插入数据");
        System.out.println("开始插入数据");
        stopWatch.start("插入数据");
//        100 70000
        long s = IdUtil.getSnowflake().nextId();
        String sql = "INSERT INTO db_cost_data_platform.gcdp_dws_index_cost\n" +
                "(id, pid, index_project_note_id, contract_project_id, enterprise_id, code, name, name_path, unit, jm_calculate_name, jm_calculate_value, jm_index_value, jm_index_value_include_tax, jm_index_unit, swl_calculate_name, swl_calculate_value, swl_index_value, swl_index_value_include_tax, swl_index_unit, amount, tbl_create_date, project_ld_merge_hash, project_un_ld_merge_hash, subject_ld_merge_hash, subject_un_ld_merge_hash)\n" +
                "VALUES(?, 2028908, 2080177, 14620178, '6902854225782862496', '001001001001', '基础工程', '工程总造价/预算书/建筑装饰工程/基础工程', NULL, NULL, NULL, NULL, NULL, NULL, '工程量', 0.00000, NULL, 0.00000, NULL, 0.00000, '2022-11-21 15:59:08', '9957338408141974214', '9888895252802526346', '9957338408141974214', '9888895252802526346');";
        ClassLoaderUtil.loadClass("com.mysql.cj.jdbc.Driver");
        try (Connection connect = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pst = connect.prepareStatement(sql)) {
            connect.setAutoCommit(Boolean.FALSE);
            for (int i = 1; i <= 1000; i++) {
                pst.setLong(1, IdUtil.getSnowflake().nextId());
                pst.addBatch();
            }
            pst.executeBatch();
            connect.commit();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        stopWatch.stop();
        System.out.println(stopWatch.prettyPrint(TimeUnit.SECONDS));
    }
}
